ZK Features Applicable to Database Access

This documentation is for an older version of ZK. For the latest one, please click here.

The org.zkoss.zk.ui.event.EventThreadCleanup Interface

As emphasized before, it is important to close the connection in the finally clause, such that every connection will be returned to connection pool correctly.

To make your application more robust, you could implement the EventThreadCleanup interface to close any pending connections and statements, in case that some of your application codes might forget to close them in the finally clause.

However, how to close pending connection and statements really depend on the server you are using. You have to consult the document of the server for how to write one.

Tip: In many cases, it is not necessary (and not easy) to provide such method, because most implementation of connection pooling be recycled a connection if its finalized method is called.

Access Database in EL Expressions

In additions to access database in an event listener, it is common to access database to fulfill an attribute by use of an EL expression. In the following example, we fetch the data from database and represent them with listbox by use of EL expressions.

 <zscript>
    import my.CustomerManager;
     customers = new CustomerManager().findAll(); //load from database
 </zscript>
 <listbox id="personList" width="800px" rows="5">
     <listhead>
         <listheader label="Name"/>
         <listheader label="Surname"/>
         <listheader label="Due Amount"/>
     </listhead>
     <listitem value="${each.id}" forEach="${customers}">
         <listcell label="${each.name}"/>
         <listcell label="${each.surname}"/>
         <listcell label="${each.due}"/>
     </listitem>
 </listbox>

There are several way to implement the findAll method.

Read all and Copy to a LinkedList

The simplest way is to retrieve all data in the findAll method, copy them into a list and then close the connection.

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

import javax.naming.InitialContext;
import javax.sql.*;

 public class CustomerManager {
     public List findAll() throws Exception {
         DataSource ds = (DataSource)new InitialContext()
                 .lookup("java:comp/env/jdbc/MyDB");
 
         Connection conn = null;
         Statement stmt = null;
         ResultSet rs = null;
         List results = new LinkedList();
         
         try {
             conn = ds.getConnection();
             stmt = conn.createStatement();
             rs = stmt.executeQuery("SELECT id, name, surname FROM customers");
             while (rs.next()) {
                 long id = rs.getInt("id");
                 String name = rs.getString("name");
                 String surname = rs.getString("surname");
                 results.add(new Customer(id, name, surname));
             }
             return results;
         } finally {
             if (rs != null) try { rs.close(); } catch (SQLException ex) {}
             if (stmt != null) try { stmt.close(); } catch (SQLException ex) {}
             if (conn != null) try { conn.close(); } catch (SQLException ex) {}
         }
     }
 }

Implement the org.zkoss.zk.ui.util.Initiator Interface

Instead of mixing Java codes with the view, you could use the init Directive to load the data.

 <?init class="my.AllCustomerFinder" arg0="customers"?>

 <listbox id="personList" width="800px" rows="5">
     <listhead>
         <listheader label="Name"/>
         <listheader label="Surname"/>
         <listheader label="Due Amount"/>
     </listhead>
     <listitem value="${each.id}" forEach="${customers}">
         <listcell label="${each.name}"/>
         <listcell label="${each.surname}"/>
         <listcell label="${each.due}"/>
     </listitem>
 </listbox>

Then, implement the my.CustomerFindAll class with the Initiator interface.

 import org.zkoss.zk.ui.Page;
 import org.zkoss.zk.ui.util.Initiator;

 public class AllCustomerFinder implements Initiator {
     public void doInit(Page page, Object[] args) {
         try {
             page.setVariable((String)args[0], new CustomerManager().findAll());
                 //Use setVariable to pass the result back to the page
         } catch (Exception ex) {
             throw UiException.Aide.wrap(ex);
         }
     }
     public void doCatch(Throwable ex) { //ignore
     }
     public void doFinally() { //ignore
     }
 }

Transaction and org.zkoss.zk.util.Initiator

For sophisticated application (such as distributed transaction), you might have to control the lifecyle of a transaction explicitly. If all database access is done in event listeners, there is nothing to change to make it work under ZK. You start, commit or rollback a transaction the same way as suggested in the document of your J2EE/Web server.

However, if you want the evaluation of the whole ZUML page (the Component Creation Phases) is done in the same transaction, then you, as described in the above section, could implement the Initiator interface to control the transaction lifecycle for a given page.

The skeletal implementation is illustrated as follows.

 import org.zkoss.zk.ui.Page;
 import org.zkoss.zk.ui.util.Initiator;

 public class TransInitiator implements Initiator {
     private boolean _err;
     public void doInit(Page page, Object[] args) {
         ''startTrans(); //depending the container, see below''
     }
     public void doCatch(Throwable ex) {
         _err = true;
         ''rollbackTrans(); //depending the container, see below''
     }
     public void doFinally() {
         if (!_err)
             ''commitTrans(); //depending the container, see below''
     }
 }

As depicted, the transaction starts in the doInit method, and ends in the doFinally method of the Initiator interface.

How to start, commit and rollback an transaction depends on the container you use.

J2EE Transaction and Initiator

If you are using a J2EE container, you could look up the transaction manager (javax.transaction.TransactionManager), and then invoke its begin method to start an transaction. To rollback, invoke its rollback method. To commit, invoke its commit method.

Web Containers and Initiator

If you are using a Web container without transaction managers, you could start a transaction by constructing a database connection. Then, invoke its commit and rollback methods accordingly.

 import java.sql.*;
 import javax.sql.DataSource;
 import javax.naming.InitContext;
 import org.zkoss.util.logging.Log;
 import org.zkoss.zk.ui.Page;
 import org.zkoss.zk.ui.util.Initiator;

 public class TransInitiator implements Initiator {
     private static final Log log = Log.lookup(TransInitiator.class);
     private Connection _conn;
     private boolean _err;

     public void doInit(Page page, Object[] args) {
         try {
             DataSource ds = (DataSource)new InitialContext()
                 .lookup("java:comp/env/jdbc/MyDB");
             _conn = ds.getConnection();
         } catch (Throwable ex) {
             throw UiException.Aide.wrap(ex);
         }
     }
     public void doCatch(Throwable t) {
         if (_conn != null) {
             try {
                 _err = true;
                 _conn.rollback();
             } catch (SQLException ex) {
                 log.warning("Unable to roll back", ex);
             }
         }
     }
     public void doFinally() {
         if (_conn != null) {
             try {
                 if (!_err)
                     _conn.commit();
             } catch (SQLException ex) {
                 log.warning("Failed to commit", ex);
             } finally {
                 try {
                     _conn.close();
                 } catch (SQLException ex) {
                     log.warning("Unable to close transaction", ex);
                 }
             }
         }
     }
 }